from typing import List, Dict

from sqlalchemy import func, inspect, distinct, and_, or_
from sqlalchemy.exc import SQLAlchemyError

from app.db import SessionLocal, teacher_management_sessionLocal
from app.db.model import get_class_by_tablename

from app.utils.exceptions import (InternalServerError, IllegalTimeError, WorkLogNotFoundError, InvalidUpdateFieldError,
                                  ExportTableError, InvalidParameterError, VersionConflictError, ResourceNotFoundError)

from app.utils.logger import logger

TeachingMaterial = get_class_by_tablename("teaching_material")
TeacherDetail = get_class_by_tablename("teacher_detail")


def query_material_submissions(semester, class_name, department, material_type, teacher_name: str, file_name: str,
                               course_name: str,
                               page: int, per_page: int) -> dict:
    with teacher_management_sessionLocal() as session:
        base_query = session.query(TeachingMaterial)
        if teacher_name:
            # teacher_name = session.query(TeacherDetail).filter(TeacherDetail.teacher_id == teacher_id).scalar()
            base_query = base_query.filter(TeachingMaterial.teacher.ilike(f"%{teacher_name}%"))
        if file_name:
            base_query = base_query.filter(TeachingMaterial.file_name.ilike(f"%{file_name}%"))
        if course_name:
            base_query = base_query.filter(TeachingMaterial.course.ilike(f"%{course_name}%"))
        if class_name != "all":
            base_query = base_query.filter(TeachingMaterial.class_name == class_name)
        if department != "all":
            base_query = base_query.filter(TeachingMaterial.department == department)
        query = base_query.filter(TeachingMaterial.semester == semester).filter(
            TeachingMaterial.material_type == material_type)
        records_total = query.count()
        offset = (page - 1) * per_page
        records = query.offset(offset).limit(per_page).all()
        data = [{
            "department": obj.department,
            "teacher": obj.teacher,
            "course": obj.course,
            "class_name": obj.class_name,
            "file_name": obj.file_name,
            "file_path": obj.file_path,
        } for obj in records]
        return {
            "data": data,
            "count": records_total,
        }


def query_course_plans(class_name, course, teacher_name, semester):
    with teacher_management_sessionLocal() as session:
        record = session.query(TeachingMaterial.file_name) \
            .filter(TeachingMaterial.material_type == "授课计划") \
            .filter(TeachingMaterial.class_name.ilike(f"%{class_name}%")) \
            .filter(TeachingMaterial.course == course) \
            .filter(TeachingMaterial.teacher == teacher_name) \
            .filter(TeachingMaterial.semester == semester) \
            .scalar()
        return record


def query_unique_col_value_and_material_type(col_name, material_type) -> List:
    """获取所有不重复的单列值列表（按字母排序）"""
    with teacher_management_sessionLocal() as session:
        col = getattr(TeachingMaterial, col_name)
        records = session.query(distinct(col)) \
            .filter(TeachingMaterial.material_type == material_type) \
            .order_by(col.asc()) \
            .all()
        # 提取元组中的第一个元素并过滤空值
        return [record[0] for record in records if record[0] is not None]
